package com.csjy.migration.hnnd;

import com.csjy.migration.hnnd.business.NdCustomerInfo;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * 基础数据迁移
 */
public class CommonDataMain {
  private DbUtil sourceDbUtil;
  private DbUtil targetDbUtil;
  private ProgramArgument programArgument;
  private String jwframeWorkflowDictId;
  private String availableItemNoSql = "SELECT ITEMNO FROM BT_ITEM_BASIC WHERE ITEMCODE like 'HND%'";
  private StrongUuidGenerator uuidGenerator = new StrongUuidGenerator();

  public CommonDataMain(DbUtil sourceDbUtil, DbUtil targetDbUtil, ProgramArgument programArgument) {
    this.sourceDbUtil = sourceDbUtil;
    this.targetDbUtil = targetDbUtil;
    this.programArgument = programArgument;
  }

  public void start() {
    System.out.println("========开始迁移基础数据========");
    try {
      this.createMigrationIdMapTableInTarget();
      this.createProcDefDict();
      this.migrationProcessDefinition();
      this.migrationTaskDefinition();
      this.migrationNdCustomerInfo();
    }catch(Exception e) {
      System.out.println("基础数据迁移失败,原因:"+e.getMessage());
      System.exit(0);
    }
    System.out.println("基础数据迁移完成.");
  }

  /**
   * 目标数据库中创建migration_id_map表
   */
  public void createMigrationIdMapTableInTarget() throws Exception {
    System.out.println("在目标数据库中创建migration_id_map表...");
    Connection conn = null;
    Statement stmt = null;
    try {
      String sql = "CREATE TABLE \"MIGRATION_ID_MAP\" \n" +
          "   (\t\"SOURCE_ID\" VARCHAR2(100), \n" +
          "\t\"TARGET_ID\" VARCHAR2(100), \n" +
          "\t\"SOURCE_NAME\" VARCHAR2(200), \n" +
          "\t\"TARGET_NAME\" VARCHAR2(200), \n" +
          "\t\"SOURCE_KEY\" VARCHAR2(100), \n" +
          "\t\"TARGET_KEY\" VARCHAR2(100), \n" +
          "\t\"CATEGORY\" VARCHAR2(100), \n" +
          "\t CONSTRAINT \"MIGRATION_ID_MAP_UN\" UNIQUE (\"SOURCE_ID\", \"CATEGORY\")\n" +
          "  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS \n" +
          "  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n" +
          "  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)\n" +
          "  TABLESPACE \"MOF\"  ENABLE\n" +
          "   ) SEGMENT CREATION IMMEDIATE \n" +
          "  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING\n" +
          "  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n" +
          "  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)\n" +
          "  TABLESPACE \"MOF\" ";

      conn = targetDbUtil.getConn();
      stmt = conn.createStatement();
      stmt.execute(sql);
      stmt.close();
      conn.close();

      System.out.println("migration_id_map表成功创建.");
    }catch(Exception e) {
      if(!e.getMessage().contains("name is already used by an existing object")
          && !e.getMessage().contains("名称已由现有对象使用")){
        throw new Exception("在目标数据库中创建migration_id_map表失败,原因:" + e.getMessage());
      } else {
        System.out.println("目标数据库中已存在migration_id_map表.");
      }
    } finally {

    }

  }

  /**
   * 创建流程定义分类字典
   */
  public void createProcDefDict() throws Exception {
    System.out.println("在目标数据库中新增流程定义类型字典:jwframeWorkflow...");
    try {
      String findSql = "SELECT ID FROM T_S_TYPE WHERE TYPECODE = 'jwframeWorkflow' AND TYPEGROUPID = '402889fe47eddd340147ede15c070001'";
      List<Map> mapList = targetDbUtil.selectByParams(findSql, new ArrayList());

      if (mapList.size() == 0) {
        String sql = "INSERT INTO T_S_TYPE\n" +
            "(ID, TYPECODE, TYPENAME, TYPEPID, TYPEGROUPID, CREATE_DATE, CREATE_NAME)\n" +
            "VALUES(?,?,?,?,?,?,?)\n";
        List values = new ArrayList();
        String id = uuidGenerator.getNextId();
        values.add(id);
        values.add("jwframeWorkflow");
        values.add("老系统流程");
        values.add(null);
        values.add("402889fe47eddd340147ede15c070001");
        values.add(new java.sql.Timestamp(System.currentTimeMillis()));
        values.add("管理员");
        targetDbUtil.updateByParams(sql, values);
        this.jwframeWorkflowDictId = id;
      } else {
        this.jwframeWorkflowDictId = mapList.get(0).get("ID").toString();
      }
    }catch(Exception e) {
      throw new Exception("流程定义类型字典创建失败,原因:" + e.getMessage());
    }
    System.out.println("流程定义类型字典:jwframeWorkflow新增成功,对应字典记录ID："+ jwframeWorkflowDictId);
  }

  /**
   * 迁移流程定义
   */
  public void migrationProcessDefinition() throws Exception {
    // ========源数据库相关表=========//
    // BT_ITEM_WORKFLOW_REC
    // JBPM_PROCESSDEFINITION
    // JBPM_MODULEDEFINITION
    // JBPM_BYTEARRAY
    // JBPM_BYTEBLOCK

    // ========目标数据库相关表=======//
    // ACT_RE_PROCDEF
    // ACT_RE_DEPLOYMENT
    // ACT_GE_BYTEARRAY
    // T_P_PROCESS
    // T_S_BUSCONFIG
    // MIGRATION_ID_MAP(source_id,source_name,category,target_id,target_name,target_key)

    // 创建数据库工具类
    System.out.println("开始迁移流程定义数据...");
    try {
      // 查询出源数据库中所有的流程定义
      String sQueryProcDefListSql =
          "      SELECT * FROM JBPM_PROCESSDEFINITION jp WHERE jp.ID_ IN (\n" +
              "        SELECT\n" +
              "          jp3.ID_\n" +
              "        FROM\n" +
              "          BT_ITEM_WORKFLOW_REC biwr\n" +
              "        JOIN JBPM_PROCESSINSTANCE jp2 ON biwr.workflowno = jp2.ID_\n" +
              "        JOIN JBPM_PROCESSDEFINITION jp3 ON jp2.PROCESSDEFINITION_ = jp3.ID_\n" +
              "        WHERE\n" +
              "          biwr.ITEMNO IN ("+ availableItemNoSql+")\n" +
              "        GROUP BY jp3.ID_\n" +
              "      ) ORDER BY jp.NAME_";

      List<Map> procDefList = sourceDbUtil.selectByParams(sQueryProcDefListSql, new ArrayList());

      // 移除掉已完成迁移的流程定义
      removeIfMigrated(procDefList,"process_definition");

      // 流程id-流程名称
      String findProcessNameAndDisplayNameSql = "\n" +
          "SELECT biwr.WFNAME ,biwr.WFDISPLAYNAME ,count(*) as COUNT_ FROM BT_ITEM_WORKFLOW_REC biwr \n" +
          "WHERE biwr.ITEMNO in ("+ availableItemNoSql+") \n" +
          "GROUP BY biwr.WFNAME ,BIWR .WFDISPLAYNAME \n" +
          "ORDER BY biwr.WFNAME ,count(*) DESC ";

      List<Map> processNameAndDisplayNameList = sourceDbUtil.selectByParams(findProcessNameAndDisplayNameSql, new ArrayList());

      for (Map row : procDefList) {
        // 查询流程显示名称
        Optional<Map> processNameAndDisplayNameMap = processNameAndDisplayNameList.stream()
            .filter(pdn->pdn.get("WFNAME").toString().equals(row.get("NAME_").toString()))
            .max(Comparator.comparing(a -> ((BigDecimal) a.get("COUNT_"))));

        String processDisplayName = processNameAndDisplayNameMap.get().get("WFDISPLAYNAME").toString();

        // 插入T_P_PROCESS(根据row.get("NAME_")进行判断)
        String processKey = insertTPProcess(row, processDisplayName);

        // 插入ACT_RE_PROCDEF
        insertActReProcDef(row, processDisplayName, processKey);
      }
      System.out.println("流程定义数据迁移完毕.");
    } catch (Exception e) {
      throw new Exception("流程定义数据迁移过程中出现异常,:" + e.getMessage());
    }
  }

  /**
   * T_P_PROCESS
   * 通过将PROCESSSTATE设置为3来区分历史流程
   */
  public String insertTPProcess(Map sourceRow, String processDisplayName) throws SQLException {
    String processName = processDisplayName + "(" + sourceRow.get("NAME_").toString() + ")";

    // 根据名称查询是否已存在,若已存在,则返回processkey
    String findSql = "SELECT PROCESSKEY FROM T_P_PROCESS WHERE PROCESSNAME = ? AND (PROCESSXMNAME = ? OR NOTE = ? OR TYPEID = ?)";
    List findParams = new ArrayList();

    findParams.add(processName);
    findParams.add(sourceRow.get("NAME_").toString());
    findParams.add(sourceRow.get("NAME_").toString());
    findParams.add(this.jwframeWorkflowDictId);
    List<Map> findResult = targetDbUtil.selectByParams(findSql,findParams);
    if(findResult.size() > 0){
      return findResult.get(0).get("PROCESSKEY").toString();
    }

    // 不存在则插入
    String processKey = "process" + System.currentTimeMillis();
    String processId = this.uuidGenerator.getNextId();

    String sql = "INSERT INTO T_P_PROCESS\n" +
        "(ID, TYPEID, USERID, PROCESSNAME, PROCESSXMLPATH, PROCESSXMNAME, NOTE, " +
        "UPDATE_NAME, UPDATE_DATE, UPDATE_BY, CREATE_NAME, CREATE_DATE, CREATE_BY, PROCESSKEY, PROCESSSTATE, PROCESSXML)\n" +
        "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    List values = new ArrayList();
    values.add(processId);
    values.add(this.jwframeWorkflowDictId); // T_S_TYPE表: 常用流程记录类
    values.add(null);
    values.add(processName);
    values.add(null);
    values.add(sourceRow.get("NAME_").toString());
    values.add(sourceRow.get("NAME_").toString());

    Date createDateMs = new java.sql.Date(System.currentTimeMillis());

    values.add("管理员");
    values.add(createDateMs);
    values.add("admin");

    values.add("管理员");
    values.add(createDateMs);
    values.add("admin");

    values.add(processKey);
    values.add(1); // 1：已发布  2：未发布
    values.add(null);

    targetDbUtil.updateByParams(sql,values);
    this.insertTSBusConfig(processId, processDisplayName, sourceRow.get("NAME_").toString());
    return processKey;
  }

  /**
   * T_S_BUSCONFIG
   * @param processId
   * @param processName

   * @throws SQLException
   */
  public void insertTSBusConfig(String processId, String processDisplayName, String processName) throws SQLException {
    String sql = "INSERT INTO T_S_BUSCONFIG\n" +
        "(ID, BUSNAME, FORM_TYPE, TABLEID, ONLINE_ID, PROCESSID, BUS_TITLE_EXP, PROCESS_DEAL_STYLE)" +
        "VALUES(?,?,?,?,?,?,?,?)";

    List values = new ArrayList();
    values.add(this.uuidGenerator.getNextId());
    values.add(processDisplayName);
    values.add("online");
    values.add(null);
    // TODO: ONLINE_ID需要界面上设置
    values.add(null);
    values.add(processId);
    values.add("老系统-"+processDisplayName+"("+processName+")");
    values.add("ndCustom");

    targetDbUtil.updateByParams(sql,values);
  }

  /**
   * ACT_RE_PROCDEF
   * @param sourceRow
   * @param processKey
   * @throws SQLException
   * @throws IOException
   */
  public void insertActReProcDef(Map<String,Object> sourceRow,String processDisplayName, String processKey) throws SQLException, IOException {
    String sql = "INSERT INTO ACT_RE_PROCDEF\n" +
        "(ID_, REV_, CATEGORY_, NAME_, KEY_, VERSION_, DEPLOYMENT_ID_, RESOURCE_NAME_, DGRM_RESOURCE_NAME_, DESCRIPTION_, HAS_START_FORM_KEY_, SUSPENSION_STATE_, TENANT_ID_)\n" +
        "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";

    String deploymentId = uuidGenerator.getNextId();
    String targetProcDefId =processKey+":"+sourceRow.get("VERSION_")+":"+deploymentId;

    List values = new ArrayList();

    // ID_组成：KEY_+VERSION_+deploymentID
    values.add(targetProcDefId);
    // REV_
    values.add(1);
    // CATEGORY_
    values.add("http://www.jeecg.org");
    // NAME_
    values.add(processDisplayName);
    // KEY_
    values.add(processKey);
    // VERSION_
    values.add(sourceRow.get("VERSION_"));
    // DEPLOYMENT_ID_
    values.add(deploymentId);
    // RESOURCE_NAME_ 组成:key_.bpmn
    values.add(deploymentId+".bpmn");
    // DGRM_RESOURCE_NAME_ 组成:key_.key_.png
    values.add(deploymentId+"."+deploymentId+".png");
    // DESCRIPTION_ 这里将老系统中流程定义的ID_放在这个字段中
    values.add(sourceRow.get("ID_").toString());
    // HAS_START_FORM_KEY_
    values.add(0);
    // SUSPENSION_STATE_
    values.add(1);
    // TENANT_ID_
    values.add(null);

    targetDbUtil.updateByParams(sql,values);

    //====== 插入ACT_RE_DEPLOYMENT记录 =======//
    this.insertActReDeployment(sourceRow.get("ID_").toString(), deploymentId, processKey);

    // 插入MIGRATION_ID_MAP(source_id,target_id,source_name,target_name,target_key,category)
    targetDbUtil.insertMigrationIdMap(
        sourceRow.get("ID_").toString()
        , targetProcDefId
        , sourceRow.get("NAME_").toString()
        , sourceRow.get("NAME_").toString()
        ,null
        ,processKey
        ,"process_definition");
  }

  /**
   * ACT_RE_DEPLOYMENT
   * @param sourceProcDefId
   * @param deploymentId
   * @param processKey
   * @throws SQLException
   * @throws IOException
   */
  public void insertActReDeployment(String sourceProcDefId, String deploymentId,String processKey) throws SQLException, IOException {
    String sql = "INSERT INTO ACT_RE_DEPLOYMENT\n" +
        "(ID_, NAME_, CATEGORY_, TENANT_ID_, DEPLOY_TIME_)\n" +
        "VALUES(?,?,?,?,?)\n";

    List values = new ArrayList();
    values.add(deploymentId);
    values.add(processKey);
    values.add(null);
    values.add(null);
    values.add(new java.sql.Timestamp(System.currentTimeMillis()));
    targetDbUtil.updateByParams(sql, values);

    // ====== 插入ACT_GE_BYTEARRAY记录========//
    this.insertActGeByteArray(sourceProcDefId, deploymentId,processKey+".bpmn",0);
    this.insertActGeByteArray(sourceProcDefId, deploymentId,processKey+"."+processKey+".png",1);
  }

  /**
   * ACT_GE_BYTEARRAY
   * @param deploymentId
   * @param name
   * @param generated
   * @throws SQLException
   */
  public void insertActGeByteArray(String sourceProcDefId, String deploymentId, String name, int generated) throws SQLException, IOException {
    String queryFileBytesSql = "\n" +
        " \tSELECT jb2.BYTES_ FROM JBPM_BYTEBLOCK jb2 WHERE jb2.PROCESSFILE_ IN (\t\n" +
        "\t \tSELECT jb.ID_ FROM JBPM_BYTEARRAY jb WHERE jb.FILEDEFINITION_ IN (\n" +
        "\t \t\tSELECT jm.ID_ FROM JBPM_MODULEDEFINITION jm \n" +
        "\t \t\tWHERE jm.PROCESSDEFINITION_  = ? AND jm.CLASS_ = 'F'\n" +
        "\t \t) AND jb.NAME_  LIKE ?\n" +
        " \t) ORDER BY jb2.PROCESSFILE_ , jb2.INDEX_ ";

    List queryFileBytesParams = new ArrayList();
    queryFileBytesParams.add(sourceProcDefId);
    queryFileBytesParams.add("%"+(generated == 0 ? ".xml" : ".jpg"));
    List<Map> sourceByteBlockMap = sourceDbUtil.selectByParams(queryFileBytesSql,queryFileBytesParams);

    ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

    for(Map map : sourceByteBlockMap) {
      byteArrayOutputStream.write((byte[])map.get("BYTES_"));
    }

    String sql = "INSERT INTO ACT_GE_BYTEARRAY\n" +
        "(ID_, REV_, NAME_, DEPLOYMENT_ID_, BYTES_, GENERATED_)\n" +
        "VALUES(?,?,?,?,?,?)";

    List values = new ArrayList();
    values.add(this.uuidGenerator.getNextId());
    values.add(1);
    values.add(name);
    values.add(deploymentId);
    values.add(new ByteArrayInputStream(byteArrayOutputStream.toByteArray()));
    values.add(generated);

    targetDbUtil.updateByParams(sql, values);

    byteArrayOutputStream.close();
  }

  /**
   * 迁移任务定义
   */
  public void migrationTaskDefinition() throws Exception {
    System.out.println("开始迁移任务定义数据...");
    try {
      // 查询出源数据库中所有的任务定义（实际流程实例对应的流程定义)
      String findSql =
          "SELECT jpd.NAME_ AS PROCDEF_NAME_,jt.NAME_ \n" +
              "FROM JBPM_TASK jt \n" +
              "JOIN JBPM_PROCESSDEFINITION jpd ON jt.PROCESSDEFINITION_ = jpd.ID_ \n"+
              "WHERE jt.PROCESSDEFINITION_ IN (\n"+
              "      SELECT jp.ID_ FROM JBPM_PROCESSDEFINITION jp WHERE jp.ID_ IN (\n" +
              "        SELECT\n" +
              "          jp3.ID_\n" +
              "        FROM\n" +
              "          BT_ITEM_WORKFLOW_REC biwr\n" +
              "        JOIN JBPM_PROCESSINSTANCE jp2 ON biwr.workflowno = jp2.ID_\n" +
              "        JOIN JBPM_PROCESSDEFINITION jp3 ON jp2.PROCESSDEFINITION_ = jp3.ID_\n" +
              "        WHERE\n" +
              "          biwr.ITEMNO IN ("+ availableItemNoSql+")\n" +
              "        GROUP BY jp3.ID_\n" +
              "      ) \n" +
              ") " +
              "GROUP BY jpd.NAME_,jt.NAME_ \n" +
              "ORDER BY jpd.NAME_";

      List<Map> taskDefList = sourceDbUtil.selectByParams(findSql, new ArrayList());

      // 移除掉已完成迁移的任务定义
      this.removeIfMigrated(taskDefList,"task_definition");

      // 查询processKey
      String findTargetTPProcessIdSql = "\n" +
          "SELECT\n" +
          "\ttpp.ID,tpp.PROCESSNAME ,tpp.NOTE \n" +
          "FROM\n" +
          "\tT_P_PROCESS tpp";
      List<Map> targetTPProcessIdList = targetDbUtil.selectByParams(findTargetTPProcessIdSql, new ArrayList());

      for (Map row : taskDefList) {
        Optional<Map> tpprocess =  targetTPProcessIdList.stream()
            .filter(tpprocessRow->
                tpprocessRow.get("PROCESSNAME").toString().equals(row.get("PROCDEF_NAME_").toString())
                    || tpprocessRow.get("NOTE").toString().equals(row.get("PROCDEF_NAME_").toString())
            )
            .findFirst();
        Thread.sleep(50);

        // 插入T_P_PROCESSNODE
        this.insertTPProcessNode(row, tpprocess.get().get("ID").toString());
      }
      System.out.println("任务定义数据迁移完毕.");
    } catch (Exception e) {
      throw new Exception("任务定义数据迁移过程中出现异常,"+ e.getMessage());
    }
  }

  /**
   * T_P_PROCESSNODE
   */
  public void insertTPProcessNode(Map sourceRow,String processId) throws SQLException {

    String sql = "INSERT INTO T_P_PROCESSNODE\n" +
        "(ID, FORMID, PROCESSID, PROCESSNODECODE, PROCESSNODENAME, MODELANDVIEW, MODELANDVIEW_MOBILE, NODE_TIMEOUT)\n" +
        "VALUES(?,?,?,?,?,?,?,?)";

    String taskId = this.uuidGenerator.getNextId();
    String taskKey = "task" + System.currentTimeMillis();
    String taskName = sourceRow.get("NAME_").toString();


    List values = new ArrayList();
    values.add(taskId);
    values.add(null);
    values.add(processId);
    values.add(taskKey);
    // 环节名称前加上对应流程定义的版本号
    values.add(taskName);
    // TODO:MODELANDVIEW 后续迁移业务数据时要一个个的设置上去
    values.add(null);
    values.add(null);
    values.add(null);

    targetDbUtil.updateByParams(sql,values);

    // 插入MIGRATION_ID_MAP
    targetDbUtil.insertMigrationIdMap(
        sourceRow.get("PROCDEF_NAME_").toString()+"@"+sourceRow.get("NAME_").toString(),
        taskId,
        sourceRow.get("NAME_").toString(),
        taskName,
        null,
        taskKey,
        "task_definition"
    );
  }

  /**
   * 移除已迁移的记录
   * @param sourceList
   * @param migrationCategory
   * @return
   * @throws SQLException
   */
  public Integer removeIfMigrated(List<Map> sourceList, String migrationCategory) throws SQLException {
    // 查询出目标数据库中已完成迁移的实例
    String tQueryMigrationListSql = "SELECT source_id FROM MIGRATION_ID_MAP WHERE CATEGORY = ? ";
    List values = new ArrayList();
    values.add(migrationCategory);
    List<Map> migratedList = targetDbUtil.selectByParams(tQueryMigrationListSql, values);
    // 移除掉已完成迁移

    if(migrationCategory.equals("task_definition")) {
      sourceList.removeIf(row-> migratedList.stream()
          .anyMatch(migrationRow ->migrationRow.get("SOURCE_ID").toString().equals(
              row.get("PROCDEF_NAME_").toString()+"@"+row.get("NAME_").toString()
          )));
    } else {
      sourceList.removeIf(row-> migratedList.stream()
          .anyMatch(migrationRow ->migrationRow.get("SOURCE_ID").toString().equals(row.get("ID_").toString())));
    }

    return migratedList.size();
  }

  /**
   * 迁移客户基本信息
   */
  public void migrationNdCustomerInfo() throws Exception {
    System.out.println("开始迁移客户基本信息...");
    try {
      NdCustomerInfo ndCustomerInfo = new NdCustomerInfo(sourceDbUtil,targetDbUtil,null, programArgument, null);
      ndCustomerInfo.start();
      System.out.println("客户基本信息数据迁移完毕.");
    } catch (Exception e) {
      throw new Exception("客户基本信息数据迁移过程中出现异常,"+ e.getMessage());
    }
  }
}
